using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class CachePage : System.Web.UI.Page
{
    string connectionString =
    WebConfigurationManager.ConnectionStrings[
    "RCVDB2000"].ConnectionString;
    SqlConnection sqlConnection;
    SqlCommand sqlCommand;
    SqlDataAdapter sqlDataAdapter;     

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadDataToDropDownList();
            LoadDataToGridView(DropDownList1.SelectedValue);        
        }         

    }
    void LoadDataToDropDownList()
    {
        sqlConnection = new SqlConnection();
        sqlConnection.ConnectionString =
        connectionString;
        try
        {
            sqlConnection.Open();
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText =
            "select * from tblProvinces";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.Text;
            sqlDataAdapter
            = new SqlDataAdapter(sqlCommand);
            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            FillDataTabletToDropDonList(dataTable);
            sqlCommand.Dispose();
            sqlConnection.Close();
        }
        catch (Exception ex)
        {
            Literal1.Text += "Failed:" + ex.Message;
        }
        sqlConnection.Dispose();
    }
   
    DataTable LoadDataToDataTable(string selectQuery)
    {
        DataTable dataTable = new DataTable();
        sqlConnection = new SqlConnection();
        sqlConnection.ConnectionString =
        connectionString;
        try
        {
            sqlConnection.Open();
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = selectQuery;            
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.Text;
            sqlDataAdapter
            = new SqlDataAdapter(sqlCommand);            
            sqlDataAdapter.Fill(dataTable);             
            sqlCommand.Dispose();
            sqlConnection.Close();
        }
        catch (Exception ex)
        {
            Literal1.Text += "Failed:" + ex.Message;
        }
        sqlConnection.Dispose();
        return dataTable;
    }
    void LoadDataToGridView(string selectValue)
    {
        try
        {
            DataTable dataTable = new DataTable();
            dataTable = (DataTable) Cache["Account"];
            Literal1.Text = "Read data from Cache";
            if(dataTable == null)
            {
                Literal1.Text = "Read data from SQL Server";
                string selectQuery=
                "select UserID, FirstName+" +
                " ' ' + LastName as FullName, " +
                " Address,ProvinceID, City" +
                " from tblJSAccounts";
                dataTable = LoadDataToDataTable(selectQuery);
                Cache["Account"] = dataTable;
                
                 
            }
            DataView dataView = new
            DataView(dataTable);
            dataView.RowFilter =
            "ProvinceID = '" + selectValue + "'";
            GridView1.DataSource = dataView;
            GridView1.DataBind();            
        }
        catch (Exception ex)
        {
            Literal1.Text += "Failed:" + ex.Message;
        }

    }
    void FillDataTabletToDropDonList(DataTable dataTable)
    {
        DropDownList1.DataSource = dataTable;
        DropDownList1.DataTextField = "ProvinceName";
        DropDownList1.DataValueField = "ProvinceID";
        DropDownList1.DataBind();
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadDataToGridView(DropDownList1.SelectedValue);
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Cache.Remove("Account");
    }
}
